﻿Imports System.IO
Imports System.Runtime.Intrinsics.X86
Imports System.Windows.Forms.DataVisualization.Charting
Imports Microsoft.Data.Sqlite

Public Class 健康管理
    Public Control_item As String
    Public Target_value, meas_value, UP_UCL, LOW_UCL, UP_SPEC, LOW_SPEC As String
    Public UID As Integer

    Private Sub 健康管理_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Width = 主窗体.TableLayoutPanel1.ColumnStyles(1).Width
        GroupBox1.Width = Me.Width
        DataGridView1.Height = 140
    End Sub

    Private Sub ComboBox1_Click(sender As Object, e As EventArgs) Handles ComboBox1.Click
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                Dim strsql As String = "SELECT 分类 FROM dictionary where 项目=@health ORDER BY 序号"
                Using cmd As New SqliteCommand(strsql, connection)
                    cmd.Parameters.AddWithValue("@health", "health")
                    Using reader As SqliteDataReader = cmd.ExecuteReader()
                        Dim dataTable As New DataTable()
                        dataTable.Load(reader)
                        If dataTable.Rows.Count > 0 Then
                            ComboBox1.DataSource = dataTable
                            ComboBox1.DisplayMember = "分类"
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Debug.WriteLine($"异常详情：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Sub
    Public Sub QueryAndShowData()
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                If ComboBox1.Text = "" Then
                    Dim strsql As String = "SELECT * FROM health ORDER BY 更新时间 limit 100"
                    Using cmd As New SqliteCommand(strsql, connection)
                        Using reader As SqliteDataReader = cmd.ExecuteReader()
                            Dim dataTable As New DataTable()
                            dataTable.Load(reader)
                            If dataTable.Rows.Count > 0 Then
                                DataGridView1.DataSource = dataTable

                            End If
                        End Using
                    End Using
                Else
                    Dim strsql As String = "SELECT * FROM health where 管控项=@sort ORDER BY 更新时间 limit 100"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@sort", ComboBox1.Text)
                        Using reader As SqliteDataReader = cmd.ExecuteReader()
                            Dim dataTable As New DataTable()
                            dataTable.Load(reader)
                            If dataTable.Rows.Count > 0 Then
                                DataGridView1.DataSource = dataTable
                            End If
                        End Using
                    End Using
                End If
            Catch ex As Exception
                Debug.WriteLine($"异常详情：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using

    End Sub
    Private Sub ConfigureGridViewColumns()
        With DataGridView1
            If DataGridView1 IsNot Nothing Then
                .Columns("选择").Width = 40
                .Columns("序号").Width = 40
                .Columns("目标值").Width = 60
                .Columns("测量值").Width = 60
                .Columns("管控项").Width = 60
                .Columns("控制上限").Width = 70
                .Columns("控制下限").Width = 70
                .Columns("规格上限").Width = 70
                .Columns("规格下限").Width = 70
                .Columns("更新时间").Width = 115
                .Columns("序号").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells  ' 包含标题行
            End If
        End With
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        QueryAndShowData()
        ConfigureGridViewColumns()
        DrawChart()
    End Sub

    Private Sub Label9_Click(sender As Object, e As EventArgs) Handles Label9.Click

    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Dim body_height, body_weight As Double
        Dim bmi As String
        body_height = Val(TextBox7.Text) / 100
        body_weight = Val(TextBox8.Text)
        bmi = (body_weight / (body_height ^ 2)).ToString("0.00")
        TextBox3.Text = bmi

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Control_item = Trim(ComboBox1.Text)
        Target_value = Val(TextBox1.Text)
        meas_value = Val(TextBox3.Text)
        UP_UCL = Val(TextBox2.Text)
        LOW_UCL = Val(TextBox4.Text)
        UP_SPEC = Val(TextBox5.Text)
        LOW_SPEC = Val(TextBox6.Text)
        If Control_item = "" Or Target_value = "" Or meas_value = "" Or UP_UCL = "" Or LOW_UCL = "" Or UP_SPEC = "" Or LOW_SPEC = "" Then
            MsgBox（"请完整填写内容"， vbOKOnly, "提示"） : Exit Sub
        Else
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "INSERT INTO health (管控项,测量值,目标值,规格上限,规格下限,控制上限,控制下限,更新时间) VALUES(@ct, @me, @ta, @sup, @sul, @cup, @cul,@updateTime)"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@ct", Control_item)
                        cmd.Parameters.AddWithValue("@me", meas_value)
                        cmd.Parameters.AddWithValue("@ta", Target_value)
                        cmd.Parameters.AddWithValue("@sup", UP_SPEC)
                        cmd.Parameters.AddWithValue("@sul", LOW_SPEC)
                        cmd.Parameters.AddWithValue("@cup", UP_UCL)
                        cmd.Parameters.AddWithValue("@cul", LOW_UCL)
                        cmd.Parameters.AddWithValue("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm"))
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            QueryAndShowData()
            DrawChart()
            MsgBox（"新增记录成功"， vbOKOnly, "提示"）
        End If
    End Sub
    Private previousCheckedRow As Integer = -1
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.RowIndex < 0 OrElse e.ColumnIndex <> 0 Then Exit Sub

        If e.ColumnIndex = 0 AndAlso e.RowIndex >= 0 Then '假设复选框列为第0列
            If previousCheckedRow <> -1 AndAlso previousCheckedRow <> e.RowIndex Then
                DataGridView1.Rows(previousCheckedRow).Cells(0).Value = False
            End If
            DataGridView1.Rows(e.RowIndex).Cells(0).Value = Not DataGridView1.Rows(e.RowIndex).Cells(0).Value
            previousCheckedRow = e.RowIndex
        End If
        Try
            Dim currentRow = DataGridView1.Rows(e.RowIndex)
            UID = currentRow.Cells("序号").Value
            ComboBox1.Text = currentRow.Cells("管控项").Value
            TextBox1.Text = currentRow.Cells("目标值").Value
            TextBox3.Text = currentRow.Cells("测量值").Value
            TextBox2.Text = currentRow.Cells("控制上限").Value
            TextBox4.Text = currentRow.Cells("控制下限").Value
            TextBox5.Text = currentRow.Cells("规格上限").Value
            TextBox6.Text = currentRow.Cells("规格下限").Value
        Catch ex As Exception
            MessageBox.Show($"操作异常：{ex.Message}")
        End Try
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Control_item = Trim(ComboBox1.Text)
        Target_value = Val(TextBox1.Text)
        meas_value = Val(TextBox3.Text)
        UP_UCL = Val(TextBox2.Text)
        LOW_UCL = Val(TextBox4.Text)
        UP_SPEC = Val(TextBox5.Text)
        LOW_SPEC = Val(TextBox6.Text)
        If Control_item = "" Or Target_value = "" Or meas_value = "" Or UP_UCL = "" Or LOW_UCL = "" Or UP_SPEC = "" Or LOW_SPEC = "" Then
            MsgBox（"请完整填写内容"， vbOKOnly, "提示"） : Exit Sub
        Else
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "UPDATE health SET 管控项 = @ct,测量值 = @me,目标值 = @ta,规格上限 = @sup,规格下限 = @sul,控制上限 = @cup,控制下限 = @cul,更新时间 = @updateTime WHERE 序号=@uid"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@uid", UID)
                        cmd.Parameters.AddWithValue("@ct", Control_item)
                        cmd.Parameters.AddWithValue("@me", meas_value)
                        cmd.Parameters.AddWithValue("@ta", Target_value)
                        cmd.Parameters.AddWithValue("@sup", UP_SPEC)
                        cmd.Parameters.AddWithValue("@sul", LOW_SPEC)
                        cmd.Parameters.AddWithValue("@cup", UP_UCL)
                        cmd.Parameters.AddWithValue("@cul", LOW_UCL)
                        cmd.Parameters.AddWithValue("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm"))
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            QueryAndShowData()
            DrawChart()
            MsgBox（"修改记录成功"， vbOKOnly, "提示"）
        End If
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim result As DialogResult = MessageBox.Show("确定要删除这条记录吗？", "确认删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If result = DialogResult.Yes Then
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "delete from health WHERE 序号 = @uid"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@uid", UID)
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            QueryAndShowData()
            DrawChart()
        End If
        MsgBox（"删除记录成功"， vbOKOnly, "提示"）
    End Sub

    Private Sub DrawChart()
        Try
            ' 初始化图表
            Chart1.Series.Clear()
            Chart1.ChartAreas(0).AxisX.IntervalType = DateTimeIntervalType.Number

            ' 创建复合分类轴数据表
            Dim compositeTable As New DataTable()
            compositeTable.Columns.Add("X分类轴", GetType(String)) ' 修改为 String 类型
            compositeTable.Columns.Add("目标值", GetType(Double))
            compositeTable.Columns.Add("测量值", GetType(Double))
            compositeTable.Columns.Add("控制上限", GetType(Double))
            compositeTable.Columns.Add("控制下限", GetType(Double))
            compositeTable.Columns.Add("规格上限", GetType(Double))
            compositeTable.Columns.Add("规格下限", GetType(Double))

            ' 数据处理
            For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                    Dim timeCell As DataGridViewCell = row.Cells("更新时间")
                    Dim controlItemCell As DataGridViewCell = row.Cells("管控项")
                    If controlItemCell.Value IsNot Nothing AndAlso Not IsDBNull(controlItemCell.Value) AndAlso
                   timeCell.Value IsNot Nothing AndAlso Not IsDBNull(timeCell.Value) Then
                        Dim xLabel As String = controlItemCell.Value & "_" & CDate(timeCell.Value).ToString("yyyy-MM-dd HH:mm") ' 转换为字符串格式

                        Dim targetValue = GetDoubleValue(row.Cells("目标值").Value)
                        Dim measuredValue = GetDoubleValue(row.Cells("测量值").Value)
                        Dim upperControlLimit = GetDoubleValue(row.Cells("控制上限").Value)
                        Dim lowerControlLimit = GetDoubleValue(row.Cells("控制下限").Value)
                        Dim upperSpecLimit = GetDoubleValue(row.Cells("规格上限").Value)
                        Dim lowerSpecLimit = GetDoubleValue(row.Cells("规格下限").Value)

                        compositeTable.Rows.Add(xLabel, targetValue, measuredValue, upperControlLimit, lowerControlLimit, upperSpecLimit, lowerSpecLimit)
                    End If
                End If
            Next

            ' 创建系列并绑定数据
            Dim seriesTypes = New Dictionary(Of String, Color) From {
            {"目标值", Color.FromArgb(0, 150, 0)},
            {"测量值", Color.FromArgb(0, 0, 150)},
            {"控制上限", Color.FromArgb(255, 100, 100)},
            {"控制下限", Color.FromArgb(255, 100, 100)},
            {"规格上限", Color.FromArgb(250, 0, 0)},
            {"规格下限", Color.FromArgb(250, 0, 0)}
        }

            For Each kvp In seriesTypes
                Dim series As Series = Chart1.Series.Add(kvp.Key)
                series.ChartType = SeriesChartType.Line
                series.Color = kvp.Value
                series.XValueMember = "X分类轴"
                series.YValueMembers = kvp.Key
                series.IsXValueIndexed = True

                Select Case kvp.Key
                    Case "测量值"
                        'series.ChartType = SeriesChartType.Line ' 显示数据点
                        series.MarkerStyle = MarkerStyle.Circle
                        series.MarkerSize = 5 ' 设置标记大小
                        series.MarkerBorderColor = Color.Black ' 设置标记边框颜色
                        series.MarkerBorderWidth = 1 ' 设置标记边框宽度
                        ' 启用数据点标签显示
                        series.IsValueShownAsLabel = False

                    Case "控制上限", "控制下限"
                        series.ChartType = SeriesChartType.Line
                        series.BorderDashStyle = ChartDashStyle.Dash ' 设置为虚线
                End Select
            Next

            ' 高级配置
            With Chart1.ChartAreas(0)

                ' 设置 X 轴标签格式
                .AxisX.LabelStyle.Angle = 90
                .AxisX.LabelStyle.Format = "" ' 不使用时间格式，直接显示字符串

                ' 设置 Y 轴范围
                .AxisY.Title = "数据"
                .AxisY.IsStartedFromZero = False
                .AxisY.Minimum = compositeTable.AsEnumerable().Min(Function(row) row.Field(Of Double)("规格下限"))
                .AxisY.Maximum = compositeTable.AsEnumerable().Max(Function(row) row.Field(Of Double)("规格上限"))
                Dim lg As Double
                lg = (.AxisY.Maximum - .AxisY.Minimum) / 2
                .AxisY.Minimum = .AxisY.Minimum - lg
                .AxisY.Maximum = .AxisY.Maximum + lg

                ' 设置网格线为灰色虚线
                .AxisX.MajorGrid.LineDashStyle = ChartDashStyle.Dash
                .AxisX.MajorGrid.LineColor = Color.FromArgb(200, 200, 200)
                .AxisX.MinorGrid.LineDashStyle = ChartDashStyle.Dash
                .AxisX.MinorGrid.LineColor = Color.FromArgb(200, 200, 200)
                .AxisY.MajorGrid.LineDashStyle = ChartDashStyle.Dash
                .AxisY.MajorGrid.LineColor = Color.FromArgb(200, 200, 200)
                .AxisY.MinorGrid.LineDashStyle = ChartDashStyle.Dash
                .AxisY.MinorGrid.LineColor = Color.FromArgb(200, 200, 200)
            End With

            ' 数据绑定
            Chart1.DataSource = compositeTable
            Chart1.DataBind()

        Catch ex As Exception
            MessageBox.Show($"图表绘制失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            File.AppendAllText("error.log", $"[{DateTime.Now}] {ex.ToString()}{Environment.NewLine}")
        End Try
    End Sub

    Private Function GetDoubleValue(value As Object) As Double
        If value IsNot Nothing AndAlso Not IsDBNull(value) AndAlso IsNumeric(value) Then
            Return CDbl(value)
        Else
            Return 0
        End If
    End Function


End Class